<?php
namespace app\admin\library;
use PHPExcel_IOFactory;
use PHPExcel;
use app\common\model\Warehouse;
use app\common\model\Room;
use app\common\model\Goods;
use think\Db;
class Excel{
    public static function getCellName($index){
        $cell = array(
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA',

            'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX',

            'AY', 'AZ'
        );;
        return $cell[$index];
    }
    /**
     * 导出表格
     * @param result 表格数据
     * @param name 下载文件名
     */
    public static function excel($result=null,$name=null,$head_name_list=null){
        $excel = new PHPExcel();

        //2.指定当前的sheet
        $objSheet = $excel->setActiveSheetIndex(0);
        $j =0;
        foreach ($head_name_list as $head_index => $head_name) {
            $objSheet->setCellValue(self::getCellName($j).'1', $head_name);
            $i = 2;
            foreach ($result as $result_index => $result_name) {
                $indexName = self::getCellName($j) . $i;
                $result_name_item = isset($result_name[$head_index])?$result_name[$head_index]:null;
                $objSheet->setCellValue($indexName, $result_name_item);
                $i++;
            }
            $j++;
            
        }
        
        //4.保存文件
        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        //告诉浏览器以xlsx文件输出
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        //Content-Disposition 消息头指示输出内容该以何种形式展示， attachment：消息体应该被下载到本地
        //filename的值预填为下载后的文件名
        if(empty($name)){
            $time = time();
            $name = "Content-Disposition: attachment;filename=\"{$time}.xlsx\"";
        }
        header($name);
        //设置缓存存储的最大周期，超过这个时间缓存被认为过期(单位秒),
        //max-age=0 即不缓存
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }
    public static function room_list_export($where){
        $head_name_list = [
            // 'goodsinfo_picture'=>'商品图像',
            'goodsinfo_name'=>'商品名称',
            'spec'=>'尺码',
            'color'=>'颜色',
            'origin_price'=>'成本价',
            'goodsinfo_cate_info_name'=>'类别',
            'goodsinfo_brand_info_name'=>'品牌',
            'warehouseinfo_name'=>'所属仓库',
            'nums'=>'当前库存',
        ];
        if(!empty($where['brand_name']) || !empty($where['cate_name'])){
            if(!empty($where['brand_name'])){
                $goods_where['brand_id'] = Db::name('goods_brand')->where('name', $where['brand_name'])->value('id');
                unset($where['brand_name']);
            }
            if(!empty($where['cate_name'])){
                $goods_where['cate_id'] = Db::name('goods_categories')->where('name', $where['cate_name'])->value('id');
                unset($where['cate_name']);
            }
            $goodsIds = Goods::where($goods_where)->column('id');
            $where['goods'] = ['in',$goodsIds];
        }

        if(!empty($where['number'])||!empty($where['tel'])){
            $whereWarehouse = [];
            if(!empty($where['number'])){
                $whereWarehouse['number'] = ['like','%'.$where['number'].'%'];
                
                unset($where['number']);
            }
            if(!empty($where['tel'])){
                $whereWarehouse['tel'] = ['like','%'.$where['tel'].'%'];
                unset($where['tel']);
            }
            $warehouseIds = Warehouse::where($whereWarehouse)->column('id');
            $where['warehouse'] = ['in',$warehouseIds];

        }
        
        $page = max(input('get.page',0),1);
        $page_size = 10;
        $total = Room::where($where)->count();
        // $rows = Room::where($where)->with(['warehouseinfo','goodsinfo' => ['brandInfo','cateInfo']])->order('id desc')->page($page,$page_size)->select()->toArray();
        $rows = Room::where($where)->with(['warehouseinfo','goodsinfo' => ['brandInfo','cateInfo']])->order('id desc')->select()->toArray();
        foreach ($rows as $key => &$item) {
            if(!empty($item['goodsinfo'])){
                $item['goodsinfo']['picture'] = config('upload_path') . $item['goodsinfo']['picture'];
                // $item['goodsinfo']['pic_tmb'] = $this->getThumb($item['goodsinfo']['picture']);
            }
            $item['goodsinfo_picture'] = $item['goodsinfo']['picture'];
            $item['goodsinfo_name'] = $item['goodsinfo']['name'];
            $item['goodsinfo_cate_info_name'] = $item['goodsinfo']['cate_info']['name'];
            $item['goodsinfo_brand_info_name'] = $item['goodsinfo']['brand_info']['name'];
            $item['warehouseinfo_name'] = $item['warehouseinfo']['name'];
            
            
        }
        self::excel($rows,null,$head_name_list);

    }
    
}